﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references
namespace Gift.DAL.crm
{
    /// <summary>
    /// 数据访问类:t_DALGoods
    /// </summary>
    public partial class t_DALGoods
    {
        public t_DALGoods()
        { }
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int goodsId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_Goods");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)
			};
            parameters[0].Value = goodsId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Gift.Model.crm.t_Goods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_Goods(");
            strSql.Append("goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted)");
            strSql.Append(" values (");
            strSql.Append("@goodsName,@goodsCode,@brandId,@goodsPrice,@goodsOrder,@isDeleted)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsName", SqlDbType.NVarChar,250),
					new SqlParameter("@goodsCode", SqlDbType.NVarChar,150),
					new SqlParameter("@brandId", SqlDbType.Int,4),
					new SqlParameter("@goodsPrice", SqlDbType.Decimal,9),
					new SqlParameter("@goodsOrder", SqlDbType.Int,4),
					new SqlParameter("@isDeleted", SqlDbType.Bit,1)};
            parameters[0].Value = model.goodsName;
            parameters[1].Value = model.goodsCode;
            parameters[2].Value = model.brandId;
            parameters[3].Value = model.goodsPrice;
            parameters[4].Value = model.goodsOrder;
            parameters[5].Value = model.isDeleted;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Gift.Model.crm.t_Goods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_Goods set ");
            strSql.Append("goodsName=@goodsName,");
            strSql.Append("goodsCode=@goodsCode,");
            strSql.Append("brandId=@brandId,");
            strSql.Append("goodsPrice=@goodsPrice,");
            strSql.Append("goodsOrder=@goodsOrder,");
            strSql.Append("isDeleted=@isDeleted");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsName", SqlDbType.NVarChar,250),
					new SqlParameter("@goodsCode", SqlDbType.NVarChar,150),
					new SqlParameter("@brandId", SqlDbType.Int,4),
					new SqlParameter("@goodsPrice", SqlDbType.Decimal,9),
					new SqlParameter("@goodsOrder", SqlDbType.Int,4),
					new SqlParameter("@isDeleted", SqlDbType.Bit,1),
					new SqlParameter("@goodsId", SqlDbType.Int,4)};
            parameters[0].Value = model.goodsName;
            parameters[1].Value = model.goodsCode;
            parameters[2].Value = model.brandId;
            parameters[3].Value = model.goodsPrice;
            parameters[4].Value = model.goodsOrder;
            parameters[5].Value = model.isDeleted;
            parameters[6].Value = model.goodsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int goodsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update  t_Goods  set isDeleted = 1");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)
			};
            parameters[0].Value = goodsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string goodsIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_Goods  set isDeleted = 1");
            strSql.Append(" where goodsId in (" + goodsIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        

            /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.crm.t_Goods GetModelByGoodsCode(string goodsCode)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 goodsId,goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted from t_Goods ");
            strSql.Append(" where goodsCode=@goodsCode");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsCode", SqlDbType.VarChar,150)
			};
            parameters[0].Value = goodsCode;

            Gift.Model.crm.t_Goods model = new Gift.Model.crm.t_Goods();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["goodsId"] != null && ds.Tables[0].Rows[0]["goodsId"].ToString() != "")
                {
                    model.goodsId = int.Parse(ds.Tables[0].Rows[0]["goodsId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsName"] != null && ds.Tables[0].Rows[0]["goodsName"].ToString() != "")
                {
                    model.goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCode"] != null && ds.Tables[0].Rows[0]["goodsCode"].ToString() != "")
                {
                    model.goodsCode = ds.Tables[0].Rows[0]["goodsCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["brandId"] != null && ds.Tables[0].Rows[0]["brandId"].ToString() != "")
                {
                    model.brandId = int.Parse(ds.Tables[0].Rows[0]["brandId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsPrice"] != null && ds.Tables[0].Rows[0]["goodsPrice"].ToString() != "")
                {
                    model.goodsPrice = decimal.Parse(ds.Tables[0].Rows[0]["goodsPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsOrder"] != null && ds.Tables[0].Rows[0]["goodsOrder"].ToString() != "")
                {
                    model.goodsOrder = int.Parse(ds.Tables[0].Rows[0]["goodsOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isDeleted"] != null && ds.Tables[0].Rows[0]["isDeleted"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isDeleted"].ToString() == "1") || (ds.Tables[0].Rows[0]["isDeleted"].ToString().ToLower() == "true"))
                    {
                        model.isDeleted = true;
                    }
                    else
                    {
                        model.isDeleted = false;
                    }
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.crm.t_Goods GetModel(int goodsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 goodsId,goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted from t_Goods ");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)
			};
            parameters[0].Value = goodsId;

            Gift.Model.crm.t_Goods model = new Gift.Model.crm.t_Goods();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["goodsId"] != null && ds.Tables[0].Rows[0]["goodsId"].ToString() != "")
                {
                    model.goodsId = int.Parse(ds.Tables[0].Rows[0]["goodsId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsName"] != null && ds.Tables[0].Rows[0]["goodsName"].ToString() != "")
                {
                    model.goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCode"] != null && ds.Tables[0].Rows[0]["goodsCode"].ToString() != "")
                {
                    model.goodsCode = ds.Tables[0].Rows[0]["goodsCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["brandId"] != null && ds.Tables[0].Rows[0]["brandId"].ToString() != "")
                {
                    model.brandId = int.Parse(ds.Tables[0].Rows[0]["brandId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsPrice"] != null && ds.Tables[0].Rows[0]["goodsPrice"].ToString() != "")
                {
                    model.goodsPrice = decimal.Parse(ds.Tables[0].Rows[0]["goodsPrice"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsOrder"] != null && ds.Tables[0].Rows[0]["goodsOrder"].ToString() != "")
                {
                    model.goodsOrder = int.Parse(ds.Tables[0].Rows[0]["goodsOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isDeleted"] != null && ds.Tables[0].Rows[0]["isDeleted"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isDeleted"].ToString() == "1") || (ds.Tables[0].Rows[0]["isDeleted"].ToString().ToLower() == "true"))
                    {
                        model.isDeleted = true;
                    }
                    else
                    {
                        model.isDeleted = false;
                    }
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select goodsId,goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted ");
            strSql.Append(" FROM t_Goods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetListNoDeleted(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select goodsId,goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted,(select brandName from t_brand where brandId=t_goods.brandId) as brandName ");
            strSql.Append(" FROM t_Goods where isnull(isDeleted,0)=0 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" goodsId,goodsName,goodsCode,brandId,goodsPrice,goodsOrder,isDeleted ");
            strSql.Append(" FROM t_Goods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_Goods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.goodsId desc");
            }
            strSql.AppendFormat(")AS Row,*,(select brandName from  t_brand where brandId=T.brandId) as brandName    from t_Goods T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetListByOrderCode(string sOrderCode)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT *, goodsPrice * onSale as costPrice FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
       
         
                strSql.Append("order by T.goodsId desc");
            
            strSql.AppendFormat(")AS Row, T.*,isNull((select OnSale from t_CompanyOnSale where CompanyId ={0}),1) as onSale   from t_Goods T ");

                strSql.Append(" WHERE orderCode=" + sOrderCode);
            
            strSql.Append(" ) TT");
            
            return DbHelperSQL.Query(strSql.ToString());
        }


        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "t_Goods";
            parameters[1].Value = "goodsId";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

